package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao {
    private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;

    @Override
    //模糊查询需要两个参数
	public List<Business> listBusiness(String businessName,String businessAddress) {
		List<Business> list = new ArrayList<>();
//		String sql = "select * from business order by businessId";//全查询
		StringBuffer sql = new StringBuffer("select * from business where 1=1 ");
		if(businessName!=null&&!businessName.equals("")) {
			sql.append(" and businessName like '%"+businessName+"%' ");
		}
		if(businessAddress!=null&&!businessAddress.equals("")) {
			sql.append(" and businessAddress like '%"+businessAddress+"%' ");
		}
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			while(rs.next()) {
				Business business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
                business.setPassword(rs.getString("password"));
                business.setBusinessName(rs.getString("businessName"));
                business.setBusinessAddress(rs.getString("businessAddress"));
                business.setBusinessExplain(rs.getString("businessExplain"));
                business.setStartPrice(rs.getDouble("startPrice"));
                business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
                list.add(business);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs,pst,con);
		}
		return list;
	}
    @Override
    public int saveBusiness(String businessName) {
    	int businessId = 0;
    	String sql = "insert into business(businessName,password) value(?,'123')";
    	con = DBUtil.getConnection();
    	try {
			pst = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			pst.setString(1, businessName);
			pst.executeUpdate();
			rs = pst.getGeneratedKeys();
			if(rs.next()) {
				businessId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	finally {
    		DBUtil.close(rs, pst, con);
    	}
    	return businessId;
    }
    //从商家表删除记录dao层
	@Override
    public int removeBusiness(int businessId) {
    	int removedLine = 0;
    	String delFoodSql = "delete from food where businessId=?";//删食品
    	String delBusinessSql = "delete from business where businessId=?";//删商家
    	try {
    		//开启事务
    		con = DBUtil.getConnection();
    		con.setAutoCommit(false);
    		//删子表food
    		pst = con.prepareStatement(delFoodSql);
    		pst.setInt(1, businessId);
    		pst.executeUpdate();
    		//删主表business
    		pst = con.prepareStatement(delBusinessSql);
    		pst.setInt(1, businessId);
    		removedLine = pst.executeUpdate();
    		
    		con.commit();
    	} catch (SQLException e) {//删除失败，回滚
    		removedLine = 0;
    		try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
    		e.printStackTrace();
    	} finally {
    		DBUtil.close(null, pst, con);
    	}
    	
    	return removedLine;
    	
    }
	@Override
	public Business getBusinessByIdByPass(Integer businessId, String password) {
		Business business = null;
		String sql = "select * from business where businessId=? and password=?";
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, businessId);
			pst.setString(2, password);
			rs = pst.executeQuery();
			while(rs.next()) {
				business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
                business.setPassword(rs.getString("password"));
                business.setBusinessName(rs.getString("businessName"));
                business.setBusinessAddress(rs.getString("businessAddress"));
                business.setBusinessExplain(rs.getString("businessExplain"));
                business.setStartPrice(rs.getDouble("startPrice"));
                business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs,pst,con);
		}
		return business;
	}
	
	@Override
	public Business getBusinessById(Integer businessId) {
		Business business = null;
		String sql = "select * from business where businessId=?";
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, businessId);
			rs = pst.executeQuery();
			while(rs.next()) {
				business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
                business.setPassword(rs.getString("password"));
                business.setBusinessName(rs.getString("businessName"));
                business.setBusinessAddress(rs.getString("businessAddress"));
                business.setBusinessExplain(rs.getString("businessExplain"));
                business.setStartPrice(rs.getDouble("startPrice"));
                business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs,pst,con);
		}
		return business;
	}
	
	@Override
	public int updateBusiness(Business business) {	//更新商家结构体
		int result = 0;
		String sql = "update business set businessName=?,businessAddress=?,businessExplain=?,startPrice=?,deliveryPrice=? where businessId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, business.getBusinessName());
			pst.setString(2, business.getBusinessAddress());
			pst.setString(3, business.getBusinessExplain());
			pst.setDouble(4, business.getStartPrice());
			pst.setDouble(5, business.getDeliveryPrice());
			pst.setInt(6, business.getBusinessId());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, con);
		}
		return result;
	}
	
	@Override
	public int updateBusinessPassword(Integer businessId,String password) {	//更新商家密码
		int result = 0;
		String sql = "update business set password=?where businessId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, password);
			pst.setInt(2, businessId);
			result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, con);
		}
		return result;
	}
}
